package org.office.api.utils;

import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.office.api.constants.DataFormat;
import org.office.api.constants.DataType;
import org.office.api.constants.ExcelConstants;

/** 
 * excel操作工具类
 *
 * <pre> 
 * 构建组：office-api
 * 作者：eddy
 * 邮箱：1546077710@qq.com
 * 日期：2016年11月2日-下午1:58:23
 * 版权：
 * </pre>
 */
public class ExcelUtil {
	
	/**
	 * 获取文件后缀
	 *
	 * @param path
	 * @return 
	 */
	public static String getPostfix(String path) {
		if (null == path || "".equals(path.trim())) {
			return "";
		}
		
		if (path.contains(ExcelConstants.POINT)) {
			return path.substring(path.lastIndexOf(ExcelConstants.POINT) + 1, path.length());
		}
		
		return "";
	}
	
	/**
	 * Excel 获取单元格值
	 *
	 * @param hssfCell
	 * @return 
	 */
	public static String getValue(Cell cell, String beanName) throws IOException{
		String value = "";
		if(null == cell){
			return value;
		}
		
		try {
			if(DataType.DATE.key().equalsIgnoreCase(beanName)){
				Date date = null;
				try {
					date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat(DataFormat.DATE.value());
					value = sdf.format(date);
				} catch (Exception e) {
					value = cell.getStringCellValue();
					value = value.replaceAll("/", "-");
				}
			}else if(DataType.TIME.key().equalsIgnoreCase(beanName)){
				Date date = cell.getDateCellValue();
				SimpleDateFormat sdf = new SimpleDateFormat(DataFormat.TIME.value());
				value = sdf.format(date);
			}else if(DataType.DATETIME.key().equalsIgnoreCase(beanName)){
				Date date = cell.getDateCellValue();
				SimpleDateFormat sdf = new SimpleDateFormat(DataFormat.DATETIME.value());
				value = sdf.format(date);
			}else if(DataType.BOOLEAN.key().equalsIgnoreCase(beanName)){
				boolean bool = cell.getBooleanCellValue();
				value = "" + bool;
			}else if(DataType.INTEGER.key().equalsIgnoreCase(beanName)){
				DecimalFormat df = new DecimalFormat("0");
				value = df.format(cell.getNumericCellValue());
			}else if(DataType.LONG.key().equalsIgnoreCase(beanName)){
				DecimalFormat df = new DecimalFormat("0");
				value = df.format(cell.getNumericCellValue());
			}else if(DataType.DOUBLE.key().equalsIgnoreCase(beanName)){
				DecimalFormat df = new DecimalFormat("0.0000");
				value = df.format(cell.getNumericCellValue());
			}else if(DataType.STRING.key().equalsIgnoreCase(beanName)){
				try{
				value = cell.getStringCellValue().trim();
				}catch(IllegalStateException e){
					DecimalFormat df = new DecimalFormat("0");  
					value = df.format(cell.getNumericCellValue());
				}
			}else if(DataType.FORMULA.key().equalsIgnoreCase(beanName)){
				try{
					value = String.valueOf(cell.getNumericCellValue());
				}catch(IllegalStateException e){
					value = String.valueOf(cell.getRichStringCellValue());
				}
			}else{
				//TODO nothing to do.
			}
		} catch (Exception e) {
			String sheetName = cell.getSheet().getSheetName();
			int rowIndex = cell.getRowIndex();
			int columnIndex = cell.getColumnIndex();
			String msg = "工作簿【"+sheetName+"】中"
						+ "单元格【"+getCellPost(columnIndex, rowIndex)+"】读取异常"
						+ ",要求数据类型【"+DataType.get(beanName).value()+"】"
						+ ",实际数据类型【"+getCellTypeName(cell.getCellType())+"】"
						+ ",值为【"+getCellValue(cell)+"】";
			throw new IOException(msg);
		}
		
		return value;
	}
	
	/**
	 * 获取单元格数据
	 *
	 * @param cell
	 * @return 
	 */
	private static Object getCellValue(Cell cell){
		Object value = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			value = cell.getNumericCellValue();
			break;
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		case Cell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula();
			break;
		case Cell.CELL_TYPE_ERROR:
			value = cell.getErrorCellValue();
			break;
		default:
			break;
		}
		
		return value;
	}
	
	/**
	 * 获取单元格数据类型名称
	 *
	 * @param cellType
	 * @return 
	 */
	private static String getCellTypeName(int cellType){
		String cellTypeName = "未知";
		
		switch (cellType) {
		case Cell.CELL_TYPE_BOOLEAN:
			cellTypeName = "布尔";
			break;
		case Cell.CELL_TYPE_NUMERIC:
			cellTypeName = "数值";
			break;
		case Cell.CELL_TYPE_STRING:
			cellTypeName = "字符";
			break;
		case Cell.CELL_TYPE_BLANK:
			cellTypeName = "空";
			break;
		case Cell.CELL_TYPE_FORMULA:
			cellTypeName = "公式";
			break;
		case Cell.CELL_TYPE_ERROR:
			cellTypeName = "错误";
			break;
		default:
			break;
		}
		
		return cellTypeName;
	}
	
	/**
	 * 获取单元格位置：A1...AC1等
	 *
	 * @param columnIndex
	 * @param rowIndex
	 * @return
	 * @throws IOException 
	 */
	private static String getCellPost(int columnIndex, int rowIndex) throws IOException{
		if(columnIndex < 0 || rowIndex < 0){
			throw new IOException("参数不能小于0：columnIndex="+columnIndex+",rowIndex="+rowIndex);
		}
		return get25(columnIndex) + (rowIndex+1);
	}
	
	/**
	 * 单元格列坐标转换
	 *
	 * @param columnIndex
	 * @return
	 * @throws IOException 
	 */
	private static String get25(int columnIndex) throws IOException{
		if(columnIndex < 0){
			throw new IOException("参数不能小于0：columnIndex="+columnIndex);
		}
		StringBuilder builder = new StringBuilder();
		
        do {  
            if (builder.length() > 0) {
            	columnIndex--;
            }
            builder.insert(0, ((char) (columnIndex % 26 + (int) 'A')));
            columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
        } while (columnIndex > 0);
		
		return builder.toString();
	}
	
	public static void main(String[] args) throws IOException {
		System.out.println(getCellPost(28, 0));
	}
	
}
